package com.servicesDirectory.dao.contact;

import java.sql.*;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.BaseContactVO;
import java.util.LinkedList;
import java.util.List;

public class BaseContactDAOImpl implements BaseContactDAO {

    public static final String SEARCH_BY_FIRST_NAME = "firstName";
    public static final String SEARCH_BY_SUR_NAME = "surName";
    protected static Connection conn = null;
    protected static Connection conn1 = null;
    protected boolean checkInclude = false;

    public boolean isCheckInclude() {
        return checkInclude;
    }

    public void setCheckInclude(boolean checkInclude) {
        this.checkInclude = checkInclude;
    }

    protected static void initConnection()
            throws SQLException, ClassNotFoundException {

        if (conn == null) {
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            String conUrl = "jdbc:sqlserver://localhost:0:1433;databaseName=MockPJ_ServiceDirectory;";
//            conn = DriverManager.getConnection(conUrl, "sa", "12345678");
        	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection("jdbc:odbc:MockPJ_ServiceDirectory", "sa", "12345678");
        }
    }
    protected static void initConnection1()
            throws SQLException, ClassNotFoundException {

        if (conn1 == null) {
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            String conUrl = "jdbc:sqlserver://localhost:0:1433;databaseName=MockPJ_ServiceDirectory;";
//            conn = DriverManager.getConnection(conUrl, "sa", "12345678");
        	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn1 = DriverManager.getConnection("jdbc:odbc:MockPJ_ServiceDirectory", "sa", "12345678");
        }
    }

    @Override
    public boolean insert(BaseContactVO contact) throws FunctionalException, SystemException {
    	try
    	{
    		initConnection1();
    		
        	Statement st=conn1.createStatement();
        	
            String sql = "INSERT INTO Contact VALUES('"+contact.getFirstName()+"','"+contact.getSurName()+
            		"','"+contact.getContactType()+"','"+contact.getKnownAs()+
            		"','"+contact.getBestContact()+"','"+contact.getOfficePhone()+
            		"','"+contact.getHomePhone()+"','"+contact.getMobilePhone()+
            		"','"+contact.getJobRole()+"','"+contact.getJobTitle()+"','"+contact.getWorkBase()+
            		"','"+contact.getEmail()+"','"+contact.getIsActive()+"') ";
           
           
           int row=st.executeUpdate(sql);
            
            
            if(row>0)
            {
            	return true;
            }
            conn1.close();
    	}
    	catch (Exception ex) {
    		ex.printStackTrace();
        }
    	
    	
    	return false;
    }

    @Override
    public boolean update(BaseContactVO data) throws FunctionalException, SystemException {
        // TODO Auto-generated method stub
        return false;
    }

    public List<BaseContactVO> search(BaseContactVO contact) throws FunctionalException, SystemException {
        conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            /*
             * Initialize DB connection
             */
            initConnection();
            /*
             * Retrieve data from DB
             */
            if (checkInclude == false) {
                String strQuery = "SELECT ContactID,firstName,surName,mobilePhone,email,contactType,isActive "
                        + "FROM Contact WHERE 'a' = 'a' AND isActive = 1";
                if (contact.getFirstName().length() > 0) {
                    strQuery += " AND firstName LIKE '" + contact.getFirstName() + "%'";
                }
                if (contact.getSurName().length() > 0) {
                    strQuery += " AND surName LIKE '" + contact.getSurName() + "%'";
                }

                stmt = conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                List<BaseContactVO> result = new LinkedList<BaseContactVO>();
                BaseContactVO resultItem;

                while (rs.next()) {
                    resultItem = new BaseContactVO();
                    resultItem.setContactId(rs.getInt("ContactID"));
                    resultItem.setFirstName(rs.getString("firstName"));
                    resultItem.setSurName(rs.getString("surName"));
                    resultItem.setMobilePhone(rs.getString("mobilePhone"));
                    resultItem.setEmail(rs.getString("email"));
                    resultItem.setContactType(rs.getString("contactType"));
                    resultItem.setIsActive(rs.getInt("isActive"));
                    result.add(resultItem);
                }

                return result;
            } else {
                String strQuery = "SELECT firstName,surName,mobilePhone,email,contactType,isActive "
                        + "FROM Contact WHERE 'a' = 'a'";
                if (contact.getFirstName().length() > 0) {
                    strQuery += " AND firstName LIKE '" + contact.getFirstName() + "%'";
                }
                if (contact.getSurName().length() > 0) {
                    strQuery += " AND surName LIKE '" + contact.getSurName() + "%'";
                }
                stmt = conn.createStatement();
                rs = stmt.executeQuery(strQuery);

                List<BaseContactVO> result = new LinkedList<BaseContactVO>();
                BaseContactVO resultItem;

                while (rs.next()) {
                    resultItem = new BaseContactVO();
                    resultItem.setFirstName(rs.getString("firstName"));
                    resultItem.setSurName(rs.getString("surName"));
                    resultItem.setMobilePhone(rs.getString("mobilePhone"));
                    resultItem.setEmail(rs.getString("email"));
                    resultItem.setContactType(rs.getString("contactType"));
                    resultItem.setIsActive(rs.getInt("isActive"));
                    result.add(resultItem);
                }

                return result;
            }
        } catch (SQLException ex) {
        } catch (ClassNotFoundException ex) {
        } finally {
            try {
                if (rs != null) {

                    rs.close();
                }

                if (stmt != null) {

                    stmt.close();

                }
                if (conn != null) {

                    conn.close();

                }
            } catch (SQLException e) {
            }

        }
        return null;
    }
}
